{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data.json', 'r', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        json_str=line\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "item={\n",
    "  \"id\": \"Q60\",\n",
    "  \"type\": \"item\",\n",
    "  \"labels\": {},\n",
    "  \"descriptions\": {},\n",
    "  \"aliases\": {},\n",
    "  \"claims\": {},\n",
    "  \"sitelinks\": {},\n",
    "  \"lastrevid\": 195301613,\n",
    "  \"modified\": \"2020-02-10T12:42:02Z\"\n",
    "}\n",
    "{\n",
    "  \"claims\": {\n",
    "    \"P17\": [{\n",
    "        \"id\": \"q60$5083E43C-228B-4E3E-B82A-4CB20A22A3FB\",\n",
    "        \"mainsnak\": {},\n",
    "        \"type\": \"statement\",\n",
    "        \"rank\": \"normal\",\n",
    "        \"qualifiers\": {},\n",
    "        \"references\": [{}]\n",
    "      }]\n",
    "  }\n",
    "}\n",
    "\n",
    "create_items_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS items (\n",
    "\t\ttype VARCHAR(255), \n",
    "\t\tid VARCHAR(255) PRIMARY KEY, \n",
    "\t\tlabels JSON, \n",
    "\t\tdescriptions JSON, \n",
    "\t\taliases JSON, \n",
    "\t\tclaims JSON, \n",
    "\t\tsitelinks JSON, \n",
    "\t\ttitle VARCHAR(255), \n",
    "\t\tmodified VARCHAR(255)); \"\"\"\n",
    "create_item_pp_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS item_pp (\n",
    "    qid VARCHAR(255), \n",
    "    pid VARCHAR(255), \n",
    "    mainsnak JSON, \n",
    "    type VARCHAR(255), \n",
    "    qualifiers JSON, \n",
    "    qualifiers_order JSON, \n",
    "    id VARCHAR(255) PRIMARY KEY, \n",
    "    rank1 VARCHAR(255), \n",
    "    references1 JSON);\"\"\"\n",
    "\n",
    "create_claims2_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS claims2 (\n",
    "        qid VARCHAR(255),\n",
    "        type VARCHAR(255),\n",
    "        pqid VARCHAR(255),\n",
    "        from_type VARCHAR(255),\n",
    "        from_pid VARCHAR(255)\n",
    "    )\"\"\"\n",
    "\n",
    "create_claims2_rel_sql = \"\"\"CREATE TABLE IF NOT EXISTS claims2_rel (\n",
    "        qid VARCHAR(255) PRIMARY KEY,\n",
    "        qid_list JSON);\"\"\"\n",
    "\n",
    "create_claims3_sql=\"\"\"CREATE TABLE IF NOT EXISTS claims3 (\n",
    "        claims_id VARCHAR(255) PRIMARY KEY, \n",
    "        from_qid VARCHAR(255), \n",
    "        qid_list JSON);\"\"\"\n",
    "create_query3_sql=\"\"\"CREATE TABLE IF NOT EXISTS query3 (\n",
    "        qid VARCHAR(255) PRIMARY KEY, \n",
    "        qid_list JSON);\"\"\"\n",
    "create_query5_sql=\"\"\"CREATE TABLE IF NOT EXISTS query3 (\n",
    "        qid VARCHAR(255) PRIMARY KEY, \n",
    "        labels JSON,\n",
    "        des JSON,\n",
    "        property_list JSON,\n",
    "        sentences_embedding JSON);\"\"\"\n",
    "qid='Q31'\n",
    "query_claims2_sql = f\"\"\"select pqid \n",
    "from claims2\n",
    "where qid='{qid}'\"\"\"\n",
    "\n",
    "query_query2_sql = f\"\"\"select qid_list \n",
    "from query2\n",
    "where qid='{qid}'\"\"\"\n",
    "\n",
    "query_query3_sql = f\"\"\"select qid_list\n",
    "from query3\n",
    "where qid='{qid}'\"\"\"\n",
    "\n",
    "query_item_pp_sql = f\"\"\"select *\n",
    "from item_pp\n",
    "where qid='{qid}'\"\"\"\n",
    "\n",
    "query_query5_sql = f\"\"\"select qid,sentences_embedding\n",
    "from query5;\"\"\"\n",
    "\n",
    "query_query5_sql = f\"\"\"select labels,des,property_list\n",
    "from query5\n",
    "where qid='{qid}'\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import json\n",
    "# item_pp=[]\n",
    "# with open('last-all-zh.json', 'r', encoding='utf-8') as f:\n",
    "#     for line in f:\n",
    "#         json_data=json.loads(line)\n",
    "#         item={}\n",
    "#         for key,value in json_data.items():\n",
    "#             if key == 'claims':\n",
    "#                 # tmp=[]\n",
    "#                 for k,v in value.items():#p\n",
    "#                     # tmp.append(k) #只存储键\n",
    "#                     for i in v:\n",
    "#                         tmp2={}\n",
    "#                         tmp2['qid']=json_data['id']\n",
    "#                         tmp2['pid']=k\n",
    "#                         for key,value in i.items():\n",
    "#                             tmp2[key]=value\n",
    "#                         item_pp.append(tmp2)\n",
    "# keys_list=[]\n",
    "# for p in item_pp:\n",
    "#     if p.keys() not in keys_list:\n",
    "#         keys_list.append(p.keys())\n",
    "# keys_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "# 多语言的一个json格式数据，格式化后可能几万行。把其中的中文和英文提取出来，保存为新的json文件\n",
    "# 下面是只提取中文和英文数据的示例，sitelinks也只提取中文和英文，但提取的数据也可能包含了其它语言\n",
    "items=[]\n",
    "item_claims=[]\n",
    "with open('last-all-zh.json', 'r', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        json_data=json.loads(line)\n",
    "        item={}\n",
    "        for key,value in json_data.items():\n",
    "            if key == 'claims':\n",
    "                # tmp=[]\n",
    "                for k,v in value.items():#p\n",
    "                    # tmp.append(k) #只存储键\n",
    "                    tmp2={}\n",
    "                    tmp2['qid']=json_data['id']\n",
    "                    tmp2['pid']=k\n",
    "                    tmp2['values']=v\n",
    "                    item_claims.append(tmp2)    \n",
    "                item[key]={}#由于属性可能很多，导致插入失败，所以不存储属性值\n",
    "                \n",
    "            #判断是否是字典\n",
    "            elif isinstance(value, dict):\n",
    "                tmp={}\n",
    "                if 'site' in key:\n",
    "                    tmp={}\n",
    "                    for k,v in value.items():\n",
    "                        if k.startswith('enwiki') or k.startswith('zhwiki'):\n",
    "                            tmp[k]=v\n",
    "                else:\n",
    "                    for k,v in value.items():\n",
    "                        if k=='en' or k=='zh':\n",
    "                            tmp[k]=v\n",
    "                item[key]=tmp\n",
    "            else:\n",
    "                item[key]=value\n",
    "        items.append(item)\n",
    "# with open('data-zh-item.json', 'w', encoding='utf-8') as f:\n",
    "#     for item in items:\n",
    "#         f.write(json.dumps(item, ensure_ascii=False))\n",
    "#         f.write('\\n')\n",
    "with open('data-zh-item-claims.json','w',encoding='utf-8') as f:\n",
    "    for item in item_claims:\n",
    "        f.write(json.dumps(item, ensure_ascii=False))\n",
    "        f.write('\\n')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pymysql\n",
    "# 查询1的求解\n",
    "items=[]\n",
    "with open('data-zh-item.json', 'r', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        json_data=json.loads(line)\n",
    "        items.append(json_data)\n",
    "# json_data = items[0]\n",
    "# items=items[:10]\n",
    "# 根据字典创建一个mysql表\n",
    "def create_table(cursor, table_name, json_data):\n",
    "    # 创建表的SQL语句\n",
    "    create_table_sql = f\"CREATE TABLE IF NOT EXISTS {table_name} (\"\n",
    "    # 遍历json_data的键值对\n",
    "    for key, value in json_data.items():\n",
    "        # 如果值是字典，则递归调用create_table函数\n",
    "        if key == 'id':\n",
    "            create_table_sql += f\"{key} VARCHAR(255) PRIMARY KEY, \"\n",
    "        elif isinstance(value, dict):\n",
    "            create_table_sql += f\"{key} JSON, \"\n",
    "        # 如果值是字符串，则添加到创建表的SQL语句中\n",
    "        elif isinstance(value, str):\n",
    "            create_table_sql += f\"{key} VARCHAR(255), \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    create_table_sql = create_table_sql[:-2]\n",
    "    # 添加关闭括号和分号\n",
    "    create_table_sql += \");\"\n",
    "    print(create_table_sql)\n",
    "    # 执行创建表的SQL语句\n",
    "    create_table_sql='ALTER DATABASE wikidata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'\n",
    "    cursor.execute(create_table_sql)\n",
    "    create_table_sql='ALTER TABLE items CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'\n",
    "    cursor.execute(create_table_sql)\n",
    "#存储到mysql\n",
    "def store_to_mysql(cursor, table_name, json_data):\n",
    "    if query_entity_by_id(cursor,table_name,json_data['id']):\n",
    "        print(\"Data already exists.\")\n",
    "        return\n",
    "    # 插入数据的SQL语句\n",
    "    insert_data_sql = f\"INSERT INTO {table_name} VALUES (\"\n",
    "    # 遍历json_data的键值对\n",
    "    for key, value in json_data.items():\n",
    "        # 如果值是字典，则递归调用store_to_mysql函数, 加上ensure_ascii=False参数，才能很好地处理中文\n",
    "        value_str=json.dumps(value,ensure_ascii=False).replace(\"'\", \"\\\\'\").replace('\"', '\\\\\"')\n",
    "        # value_str=value_str.encode('utf-8',errors='ignore')\n",
    "        if key == 'claims':\n",
    "            insert_data_sql += \"\\'\"+f'{value_str}'+\"\\', \"\n",
    "        elif isinstance(value, dict):#存储json\n",
    "            insert_data_sql += \"\\'\"+f'{value_str}'+\"\\', \"\n",
    "        # 如果值是字符串，则添加到插入数据的SQL语句中\n",
    "        elif isinstance(value, str):\n",
    "            insert_data_sql += f\"'{value}', \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    insert_data_sql = insert_data_sql[:-2]\n",
    "    # 添加关闭括号和分号\n",
    "    insert_data_sql += \");\"\n",
    "    \n",
    "    print(f'{insert_data_sql}')\n",
    "    # print(insert_data_sql.encode('utf-8').decode('unicode_escape'))\n",
    "    # 执行插入数据的SQL语句\n",
    "    cursor.execute(insert_data_sql)\n",
    "    # 提交事务\n",
    "    conn.commit()\n",
    "    print(\"Data stored successfully.\")\n",
    "# 根据id查询实体\n",
    "def query_entity_by_id(cursor, table_name, id):\n",
    "    # 查询实体的SQL语句\n",
    "    query_entity_sql = f\"SELECT * FROM {table_name} WHERE id = '{id}';\"\n",
    "    # 执行查询实体的SQL语句\n",
    "    cursor.execute(query_entity_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchone()\n",
    "    # 返回查询结果\n",
    "    return result\n",
    "# 根据id删除实体\n",
    "def del_entity_by_id(cursor, table_name, id):\n",
    "    # 删除实体的SQL语句\n",
    "    del_entity_sql = f\"DELETE FROM {table_name} WHERE id = '{id}';\"\n",
    "    # 执行删除实体的SQL语句\n",
    "    res=cursor.execute(del_entity_sql)\n",
    "    # 提交事务\n",
    "    conn.commit()\n",
    "    print(\"Data deleted successfully.\",res)\n",
    "# 根据中文名查询实体\n",
    "def query_entity_by_zh(cursor, table_name, zh_name):\n",
    "    # 查询实体的SQL语句\n",
    "    zh_name=zh_name.replace('\"', '\\\\\"')\n",
    "    query_entity_sql = f\"\"\"SELECT *\n",
    "FROM {table_name}\n",
    "WHERE JSON_SEARCH(labels, 'one', '%{zh_name}%') IS NOT NULL\n",
    "   OR JSON_SEARCH(aliases, 'one', '%{zh_name}%') IS NOT NULL;\"\"\"\n",
    "    # 执行查询实体的SQL语句\n",
    "    cursor.execute(query_entity_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # 返回查询结果\n",
    "    print(result)\n",
    "    return result\n",
    "# 插叙items表中有多少条记录\n",
    "def query_count(cursor, table_name):\n",
    "    # 查询实体的SQL语句\n",
    "    query_entity_sql = f\"SELECT COUNT(*) FROM {table_name};\"\n",
    "    # 执行查询实体的SQL语句\n",
    "    cursor.execute(query_entity_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchone()\n",
    "    # 返回查询结果\n",
    "    print(\"Table\",table_name,\"has\",result[0],\"records.\")\n",
    "    return result[0]\n",
    "# 连接到MySQL数据库\n",
    "conn = pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# 创建表\n",
    "create_table(cursor, \"items\", json_data)\n",
    "# # 存储数据\n",
    "for item in items:\n",
    "    # del_entity_by_id(cursor, \"items\", item['id'])\n",
    "    store_to_mysql(cursor, \"items\", item)\n",
    "\n",
    "# # 删除实体\n",
    "# # del_entity_by_id(cursor, \"items\", \"Q31\")\n",
    "# # del_entity_by_id(cursor, \"items\", \"Q8\")\n",
    "# # del_entity_by_id(cursor, \"items\", \"Q23\")\n",
    "# # query_count(cursor, \"items\")\n",
    "query_entity_by_zh(cursor, \"items\", \"保罗\")\n",
    "# 关闭数据库连接\n",
    "conn.close()\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 产生item_pp\n",
    "import json\n",
    "item_claims=[]\n",
    "with open('data-zh-item-claims.json', 'r', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        json_data=json.loads(line)\n",
    "        item_claims.append(json_data)\n",
    "print(len(item_claims))\n",
    "item_pp=[]\n",
    "for ic in item_claims:\n",
    "    for i in ic['values']:\n",
    "        tmp={}\n",
    "        tmp['qid']=ic['qid']\n",
    "        tmp['pid']=ic['pid']\n",
    "        for key,value in i.items():\n",
    "            tmp[key]=value\n",
    "        item_pp.append(tmp)\n",
    "print(len(item_pp)) # 4328\n",
    "# key_list=[]\n",
    "# for p in item_pp:\n",
    "#     if p['value'].keys() not in key_list:\n",
    "#         key_list.append(p['value'].keys())\n",
    "# for i in key_list:\n",
    "#     print(i)\n",
    "# p31=[]\n",
    "# p297=[]\n",
    "# pq=[]\n",
    "# for p in item_pp:\n",
    "#     if p['pid']=='P31':\n",
    "#         p31.append(p)\n",
    "#     if p['pid']=='P297':\n",
    "#         p297.append(p)\n",
    "#     # 如果存在键值qualifiers，则加入pq\n",
    "#     if 'qualifiers' in p['value'].keys():\n",
    "#         pq.append(p)\n",
    "# print(len(p31)) #665\n",
    "# print(len(p297)) #5\n",
    "# print(len(pq)) #8\n",
    "# print(pq[900])\n",
    "# print(pq[901])\n",
    "# for key in pq[900]['value']:\n",
    "#     print(key, pq[900]['value'][key])\n",
    "\n",
    "\n",
    "# 公共属性'mainsnak', 'type', 'id', 'rank'\n",
    "\n",
    "# with open('data-zh-items-pp.json', 'w', encoding='utf-8') as f:\n",
    "#     for item in item_pp:\n",
    "#         f.write(json.dumps(item, ensure_ascii=False))\n",
    "#         f.write('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "key_list=[]\n",
    "for p in item_pp:\n",
    "    if p.keys() not in key_list:\n",
    "        key_list.append(p.keys())\n",
    "for i in key_list:\n",
    "    print(i)\n",
    "# 根据是否存在qualifiers,references来区分。\n",
    "pp0=[]\n",
    "pp1=[]\n",
    "pp2=[]\n",
    "pp3=[]\n",
    "for p in item_pp:\n",
    "    if 'qualifiers' not in p.keys() and 'references' not in p.keys():\n",
    "        pp0.append(p)\n",
    "    elif 'qualifiers' in p.keys() and 'references' not in p.keys():\n",
    "        pp1.append(p)\n",
    "    elif 'qualifiers' not in p.keys() and 'references' in p.keys():\n",
    "        pp2.append(p)\n",
    "    else:\n",
    "        pp3.append(p)\n",
    "        json_data=p\n",
    "        break\n",
    "print(len(pp0)) # \n",
    "print(len(pp1)) # 0\n",
    "print(len(pp2)) # 0\n",
    "print(len(pp3)) # 0\n",
    "# 66593\n",
    "# 18009\n",
    "# 35574\n",
    "# 22795\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建item_pp表 查询4的求解\n",
    "import json\n",
    "import pymysql\n",
    "item_pp=[]\n",
    "with open('data-zh-items-pp.json','r',encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        item_pp.append(json.loads(line))\n",
    "# 创建item_pp表\n",
    "def create_item_pp_table(cursor, table_name, json_data):\n",
    "    # 创建表的SQL语句\n",
    "    create_table_sql = f\"CREATE TABLE IF NOT EXISTS {table_name} (\"\n",
    "    # 遍历json_data的键值对\n",
    "    for key, value in json_data.items():\n",
    "        # 如果值是字典，则递归调用create_table函数\n",
    "        key=key.replace('-','_')\n",
    "        if key == 'id':\n",
    "            create_table_sql += f\"{key} VARCHAR(255) PRIMARY KEY, \"\n",
    "        elif key == 'rank':# rank关键字，不能用于字段命名。\n",
    "            create_table_sql += \"rank1 VARCHAR(255), \"\n",
    "        elif key == 'references':\n",
    "            create_table_sql += \"references1 JSON, \"\n",
    "        elif isinstance(value, dict):\n",
    "            create_table_sql += f\"{key} JSON, \"\n",
    "        elif isinstance(value, list):\n",
    "            create_table_sql += f\"{key} JSON, \"\n",
    "        # 如果值是字符串，则添加到创建表的SQL语句中\n",
    "        elif isinstance(value, str):\n",
    "            create_table_sql += f\"{key} VARCHAR(255), \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    create_table_sql = create_table_sql[:-2]\n",
    "    # 添加关闭括号和分号\n",
    "    create_table_sql += \");\"\n",
    "    print(create_table_sql)\n",
    "    # 执行创建表的SQL语句\n",
    "    cursor.execute(create_table_sql)\n",
    "\n",
    "# 向item_pp表中插入数据\n",
    "def insert_data_to_item_pp_table(cursor, table_name, json_data):\n",
    "    #如果数据已存在，则不操作\n",
    "    if query_data_from_item_pp_table(cursor, table_name, json_data):\n",
    "        print(\"Data already exists.\")\n",
    "        return\n",
    "    # 插入数据的SQL语句\n",
    "    insert_data_sql = f\"INSERT INTO {table_name}(\"\n",
    "    for key in json_data:\n",
    "        key=key.replace(\"-\",\"_\").replace(\"rank\",\"rank1\").replace(\"references\",\"references1\")\n",
    "        insert_data_sql += f\"{key},\"\n",
    "    insert_data_sql=insert_data_sql[:-1]\n",
    "    insert_data_sql += \") VALUES (\"\n",
    "    # 遍历json_data的键值对\n",
    "    for key, value in json_data.items():\n",
    "        # 如果值是字典，则递归调用store_to_mysql函数\n",
    "        if isinstance(value, dict):#可能包含 \\ ' \" 需要转义。\n",
    "            dict_str = json.dumps(value, ensure_ascii=False).replace(\"\\\\\",\"\\\\\\\\\").replace(\"'\", \"\\\\'\").replace('\"', '\\\\\"')\n",
    "            insert_data_sql += rf\"'{dict_str}', \"\n",
    "        # 如果值是字符串，则添加到插入数据的SQL语句中\n",
    "        elif isinstance(value, str):#可能包含 \\ ' \" 需要转义。\n",
    "            insert_data_sql += f\"\\'{value}\\', \"\n",
    "        else:\n",
    "            item={}\n",
    "            item['value']=value\n",
    "            dict_str = json.dumps(item, ensure_ascii=False).replace(\"\\\\\",\"\\\\\\\\\").replace(\"'\", \"\\\\'\").replace('\"', '\\\\\"')\n",
    "            insert_data_sql += rf\"'{dict_str}', \"\n",
    "            #value转\n",
    "    # 删除最后一个逗号和空格\n",
    "    insert_data_sql = insert_data_sql[:-2]\n",
    "    # 添加关闭括号和分号\n",
    "    insert_data_sql += \");\"\n",
    "    # print(insert_data_sql)\n",
    "    # 执行插入数据的SQL语句\n",
    "    cursor.execute(insert_data_sql)\n",
    "    # 提交事务\n",
    "    conn.commit()\n",
    "    print(\"Data stored successfully.\")\n",
    "\n",
    "# 删除item_pp表中的数据\n",
    "def delete_data_from_item_pp_table(cursor, table_name, json_data):\n",
    "    # 删除数据的SQL语句\n",
    "    delete_data_sql = f\"DELETE FROM {table_name} WHERE id = '{json_data['id']}';\"\n",
    "    # print(delete_data_sql)\n",
    "    # 执行删除数据的SQL语句\n",
    "    cursor.execute(delete_data_sql)\n",
    "# 查询item_pp表中的数据\n",
    "def query_data_from_item_pp_table(cursor, table_name, json_data):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE id = '{json_data['id']}';\"\n",
    "    # print(query_data_sql)\n",
    "    # 执行查询数据的SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "# 根据qid查询实体对应的所有属性\n",
    "def query_data_from_item_pp_table_by_qid(cursor, table_name, qid):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE qid = '{qid}';\"\n",
    "    # print(query_data_sql)\n",
    "    # 执行查询数据的SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "#创建数据库连接\n",
    "conn = pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "# 创建item_pp表\n",
    "for p in item_pp:\n",
    "    if 'qualifiers' in p.keys() and 'references' in p.keys():\n",
    "        json_data=p\n",
    "        break\n",
    "# #删除item_pp表\n",
    "# cursor.execute(\"DROP TABLE item_pp;\")\n",
    "# conn.commit()\n",
    "create_item_pp_table(cursor, 'item_pp', p)\n",
    "# 向item_pp表中插入数据\n",
    "insert_data_to_item_pp_table(cursor, 'item_pp', p)\n",
    "# 插入所有数据\n",
    "for p in item_pp:\n",
    "    insert_data_to_item_pp_table(cursor, 'item_pp', p)\n",
    "# 查询item_pp表中的数据\n",
    "query_data_from_item_pp_table(cursor, 'item_pp', p)# 只需要传入id即可\n",
    "# 查询4的求解\n",
    "query_data_from_item_pp_table_by_qid(cursor, 'item_pp', p['qid'])\n",
    "# 删除item_pp表中的数据\n",
    "# delete_data_from_item_pp_table(cursor, 'item_pp', item_pp[0])\n",
    "# 关闭数据库连接\n",
    "conn.close()\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "claims2=[] #存储\n",
    "cnt=0\n",
    "# print(item_pp[0]['mainsnak'])\n",
    "for p in item_pp:\n",
    "    if 'P31' == p['pid']:\n",
    "        item={}\n",
    "        item['qid']=p['qid']\n",
    "        item['type']='P31'\n",
    "        item['pqid']=p['mainsnak']['datavalue']['value']['id']\n",
    "        item['from_type']='mainsnak'\n",
    "        item['from_pid']=p['pid']\n",
    "        claims2.append(item)\n",
    "    elif 'P279' == p['pid']:\n",
    "        item={}\n",
    "        item['qid']=p['qid']\n",
    "        item['type']='P279'\n",
    "        item['pqid']=p['mainsnak']['datavalue']['value']['id']\n",
    "        item['from_type']='mainsnak'\n",
    "        item['from_pid']=p['pid']\n",
    "        claims2.append(item)\n",
    "    if 'qualifiers' in p.keys():\n",
    "        for qk,qv in p['qualifiers'].items():\n",
    "            #qv是一个列表\n",
    "            if qk=='P31':\n",
    "                for i in qv:\n",
    "                    #执行了130次\n",
    "                    item={}\n",
    "                    item['qid']=p['qid']\n",
    "                    item['type']='P31'\n",
    "                    item['pqid']=i['datavalue']['value']['id']\n",
    "                    item['from_type']='qualifiers'\n",
    "                    item['from_pid']=p['pid']\n",
    "                    claims2.append(item)\n",
    "            elif qk=='P279':\n",
    "                for i in qv:\n",
    "                    item={}\n",
    "                    item['qid']=p['qid']\n",
    "                    item['type']='P279'\n",
    "                    item['pqid']=i['datavalue']['value']['id']\n",
    "                    item['from_type']='qualifiers'\n",
    "                    item['from_pid']=p['pid']\n",
    "                    claims2.append(item)\n",
    "    if 'references' in p.keys():\n",
    "        for rp in p['references']: #p['references']是一个列表\n",
    "            for k,v in rp['snaks'].items():\n",
    "                if k=='P31':\n",
    "                    # cnt+=1\n",
    "                    item={}\n",
    "                    item['qid']=p['qid']\n",
    "                    item['type']='P31'\n",
    "                    item['pqid']=v['datavalue']['value']['id']\n",
    "                    item['from_type']='references'\n",
    "                    item['from_pid']=p['pid']\n",
    "                    claims2.append(item)\n",
    "                elif k=='P279':\n",
    "                    # cnt+=1\n",
    "                    item={}\n",
    "                    item['qid']=p['qid']\n",
    "                    item['type']='P279'\n",
    "                    item['pqid']=v['datavalue']['value']['id']\n",
    "                    item['from_type']='references'\n",
    "                    item['from_pid']=p['pid']\n",
    "                    claims2.append(item)\n",
    "   \n",
    "print('p31和p279的个数:',len(claims2)) # 665\n",
    "\n",
    "# 创建claims2表\n",
    "import pymysql\n",
    "def create_claims2_table(cursor, table_name, json_data):\n",
    "    # 创建claims2表\n",
    "    create_table_query = f\"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS {table_name} (\n",
    "        qid VARCHAR(255),\n",
    "        type VARCHAR(255),\n",
    "        pqid VARCHAR(255),\n",
    "        from_type VARCHAR(255),\n",
    "        from_pid VARCHAR(255)\n",
    "    )\n",
    "    \"\"\"\n",
    "    print(create_table_query)\n",
    "    cursor.execute(create_table_query)\n",
    "# 插入数据\n",
    "def insert_data_into_claims2_table(cursor, table_name, json_data):\n",
    "    if query_data_from_claims2_table(cursor, table_name, json_data['qid'], json_data['type'], json_data['pqid'])!=():\n",
    "        print('数据已存在')\n",
    "        return\n",
    "    # 插入数据\n",
    "    insert_query = f\"\"\"\n",
    "    INSERT INTO {table_name} (qid, type, pqid, from_type, from_pid)\n",
    "    VALUES (%s, %s, %s, %s, %s)\n",
    "    \"\"\"\n",
    "    cursor.execute(insert_query, (json_data['qid'],json_data['type'],json_data['pqid'],json_data['from_type'],json_data['from_pid']))\n",
    "    conn.commit()\n",
    "# 查询\n",
    "def query_data_from_claims2_table(cursor, table_name, qid, type_pid, pqid):\n",
    "    # 查询数据\n",
    "    query_query = f\"\"\"\n",
    "    SELECT * FROM {table_name} WHERE qid = %s AND type = %s AND pqid = %s\n",
    "    \"\"\"\n",
    "    cursor.execute(query_query, (qid, type_pid, pqid))\n",
    "    res = cursor.fetchall()\n",
    "    # print(type(res))\n",
    "    # print(res)\n",
    "    return res\n",
    "# 连接到数据库\n",
    "conn = pymysql.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    password='123456',\n",
    "    database='wikidata'\n",
    ")\n",
    "cursor = conn.cursor()\n",
    "# 创建claims2表\n",
    "create_claims2_table(cursor, 'claims2', claims2[0])\n",
    "# 插入数据\n",
    "# insert_data_into_claims2_table(cursor, 'claims2', claims2[0])\n",
    "for i in claims2:\n",
    "    insert_data_into_claims2_table(cursor, 'claims2', i)\n",
    "# 查询数据\n",
    "res=query_data_from_claims2_table(cursor, 'claims2', claims2[1]['qid'], claims2[1]['type'], claims2[1]['pqid'])\n",
    "print(res!=())\n",
    "# 关闭连接\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询2的求解 输入的claims2表，输出claims2_rel表\n",
    "p31_p={} #qid:[pid,]\n",
    "p31_c={} #qid:[cid,]\n",
    "p279_p={}\n",
    "p279_c={}\n",
    "for p in claims2:\n",
    "    if p['type']=='P31':\n",
    "        if p31_p.get(p['qid'],None) == None:\n",
    "            p31_p[p['qid']]=[p['pqid']]\n",
    "        else:\n",
    "            p31_p[p['qid']].append(p['pqid'])\n",
    "        if p31_c.get(p['pqid'],None) == None:\n",
    "            p31_c[p['pqid']]=[p['qid']]\n",
    "        else:\n",
    "            p31_c[p['pqid']].append(p['qid'])\n",
    "    elif p['type']=='P279':\n",
    "        if p279_p.get(p['qid'],None) == None:\n",
    "            p279_p[p['qid']]=[p['pqid']]\n",
    "        else:\n",
    "            p279_p[p['qid']].append(p['pqid'])\n",
    "        if p279_c.get(p['pqid'],None) == None:\n",
    "            p279_c[p['pqid']]=[p['qid']]\n",
    "        else:\n",
    "            p279_c[p['pqid']].append(p['qid'])\n",
    "# for k,v in p31_p.items():\n",
    "#     if len(v)>1: #一个实例可能属于多个类\n",
    "#         print(k,v)\n",
    "# for k,v in p31_c.items():\n",
    "#     if len(v)>1: #一个类可能包含多个实例\n",
    "#         print(k,v)\n",
    "# for k,v in p279_p.items():\n",
    "#     if len(v)>1: #一个类可能有多个父类\n",
    "#         print(k,v)\n",
    "# for k,v in p279_c.items():\n",
    "#     if len(v)>1: #一个类可能包含多个子类\n",
    "#         print(k,v)\n",
    "# 查询Q4620674的类\n",
    "q='Q31'\n",
    "ans=[]\n",
    "def dfs(q,h,visited):#查询q前面说有的类,q为待查询的实体，h为查询的层数,visited为已经访问过的实体,防止进入死循环\n",
    "    if h<=0:\n",
    "        return\n",
    "    if q in p31_p.keys():\n",
    "        for p in p31_p[q]:\n",
    "            if visited.get(p,None)==None:\n",
    "                ans.append(p)\n",
    "                visited[p]=True\n",
    "                dfs(p,h-1,visited)\n",
    "    if q in p279_p.keys():\n",
    "        for p in p279_p[q]:\n",
    "            if visited.get(p,None)==None:\n",
    "                ans.append(p)\n",
    "                visited[p]=True\n",
    "                dfs(p,h-1,visited)\n",
    "    # 也可以按照下面方法求        \n",
    "    # for p in p31_c.keys():\n",
    "    #     if q in p31_c[p]:\n",
    "    #         ans.append(p)\n",
    "    #         dfs(p)\n",
    "    # for p in p279_c.keys():\n",
    "    #     if q in p279_c[p]:\n",
    "    #         ans.append(p)\n",
    "    #         dfs(p)\n",
    "dfs(q,5,{})\n",
    "relations={}\n",
    "for q in p31_p.keys():\n",
    "    ans=[]\n",
    "    dfs(q,5,{})\n",
    "    relations[q]=ans\n",
    "for q in p279_p.keys():\n",
    "    ans=[]\n",
    "    dfs(q,5,{})\n",
    "    relations[q]=ans\n",
    "print(len(relations)) # 665\n",
    "rel_list=[]\n",
    "for k,v in relations.items():\n",
    "    item={}\n",
    "    item['qid']=k\n",
    "    item['qid_list']=v\n",
    "    rel_list.append(item)\n",
    "# 考虑当增加一个实体，修该一个实体，删除一个实体时，如何更新relations表\n",
    "# 增加一个实体， 该实体涉及的类，直接把父类的父类加入到与该实体关联的记录中\n",
    "# 修该一个实体，假设删除了该实体的某个父类。 那么在与该实体关联的记录中，删除该父类. 同时，所有父类包含该实体的记录，也需要删除。\n",
    "relations\n",
    "# 创建relations表\n",
    "def create_relations_table(cursor, table_name, data):\n",
    "    create_table_query = f\"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS {table_name} (\n",
    "        qid VARCHAR(255) PRIMARY KEY,\n",
    "        qid_list JSON\n",
    "    )\n",
    "    \"\"\"\n",
    "    print(create_table_query)\n",
    "    cursor.execute(create_table_query)\n",
    "# 插入数据\n",
    "def insert_data_into_relations_table(cursor, table_name, data):\n",
    "    if query_data_from_relations_table(cursor, table_name, data['qid'])!=():\n",
    "        return\n",
    "    item={}\n",
    "    item['value']=data['qid_list']\n",
    "    dict_str=json.dumps(item, ensure_ascii=False).replace(\"'\",\"\\\\'\").replace('\"','\\\\\"')\n",
    "    insert_sql = f\"\"\"INSERT INTO {table_name} (qid, qid_list) VALUES (\\'{data['qid']}\\',\\'{dict_str}\\');\"\"\"\n",
    "    # print(insert_sql)\n",
    "    cursor.execute(insert_sql)\n",
    "    conn.commit()\n",
    "# 查询数据\n",
    "def query_data_from_relations_table(cursor, table_name, qid):\n",
    "    query_sql = f\"\"\"SELECT * FROM {table_name} WHERE qid = %s\"\"\"\n",
    "    cursor.execute(query_sql, (qid,))\n",
    "    return cursor.fetchall()\n",
    "# 关闭连接\n",
    "def close_connection(cursor, conn):\n",
    "    cursor.close()\n",
    "    conn.close()\n",
    "\n",
    "conn=pymysql.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    password='123456',\n",
    "    database='wikidata'\n",
    ")\n",
    "cursor = conn.cursor()\n",
    "create_relations_table(cursor, 'query2', {})\n",
    "# insert_data_into_relations_table(cursor, 'query2', rel_list[0])\n",
    "for i in range(len(rel_list)):\n",
    "    insert_data_into_relations_table(cursor, 'query2', rel_list[i])\n",
    "# 查询2的求解 根据实体的qid查询其关联的类\n",
    "print(query_data_from_relations_table(cursor, 'query2', rel_list[0]['qid']))\n",
    "close_connection(cursor, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询2的求解 输入的claims2表，输出claims2_rel表\n",
    "p31_p={} #qid:[pid,]\n",
    "p31_c={} #qid:[cid,]\n",
    "p279_p={}\n",
    "p279_c={}\n",
    "for p in claims2:\n",
    "    if p['type']=='P31':\n",
    "        if p31_p.get(p['qid'],None) == None:\n",
    "            p31_p[p['qid']]=[p['pqid']]\n",
    "        else:\n",
    "            p31_p[p['qid']].append(p['pqid'])\n",
    "        if p31_c.get(p['pqid'],None) == None:\n",
    "            p31_c[p['pqid']]=[p['qid']]\n",
    "        else:\n",
    "            p31_c[p['pqid']].append(p['qid'])\n",
    "    elif p['type']=='P279':\n",
    "        if p279_p.get(p['qid'],None) == None:\n",
    "            p279_p[p['qid']]=[p['pqid']]\n",
    "        else:\n",
    "            p279_p[p['qid']].append(p['pqid'])\n",
    "        if p279_c.get(p['pqid'],None) == None:\n",
    "            p279_c[p['pqid']]=[p['qid']]\n",
    "        else:\n",
    "            p279_c[p['pqid']].append(p['qid'])\n",
    "def dfs(q,h,visited):#查询q前面说有的类,q为待查询的实体，h为查询的层数,visited为已经访问过的实体,防止进入死循环\n",
    "    if h<=0:\n",
    "        return\n",
    "    if q in p31_p.keys():\n",
    "        for p in p31_p[q]:\n",
    "            if visited.get(p,None)==None:\n",
    "                ans.append(p)\n",
    "                visited[p]=True\n",
    "                dfs(p,h-1,visited)\n",
    "    if q in p279_p.keys():\n",
    "        for p in p279_p[q]:\n",
    "            if visited.get(p,None)==None:\n",
    "                ans.append(p)\n",
    "                visited[p]=True\n",
    "                dfs(p,h-1,visited)\n",
    "relations=[]\n",
    "for q in p31_p.keys():\n",
    "    ans=[]\n",
    "    dfs(q,5,{})\n",
    "    item={}\n",
    "    item['qid']=q\n",
    "    item['qid_list']=ans\n",
    "    relations.append(item)\n",
    "for q in p279_p.keys():\n",
    "    ans=[]\n",
    "    dfs(q,5,{})\n",
    "    item={}\n",
    "    item['qid']=q\n",
    "    item['qid_list']=ans\n",
    "    relations.append(item)\n",
    "print(len(relations)) # 665"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询3的求解 产生claims3表 根据实体id查询该实体下属性中涉及的实体\n",
    "import json\n",
    "item_pp=[]\n",
    "with open('data-zh-items-pp.json','r',encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        item_pp.append(json.loads(line))\n",
    "claims3=[] #存储\n",
    "cnt=0\n",
    "# print(item_pp[0]['mainsnak'])\n",
    "for p in item_pp:\n",
    "    qid_list=[]\n",
    "    if 'wikibase-item' == p['mainsnak']['datatype'] and p['mainsnak'].get('datavalue',None) is not None:\n",
    "        qid_list.append(p['mainsnak']['datavalue']['value']['id'])\n",
    "    elif 'qualifiers' in p.keys():\n",
    "        for qk,qv in p['qualifiers'].items():\n",
    "            #qv是一个列表\n",
    "            if type(qv) is dict:\n",
    "                if 'wikibase-item' == qv['datatype'] and qv.get('datavalue',None) is not None:\n",
    "                    qid_list.append(qv['datavalue']['value']['id'])\n",
    "            elif type(qv) is list:\n",
    "                for i in qv:\n",
    "                    if 'wikibase-item' == i['datatype'] and i.get('datavalue',None) is not None:\n",
    "                        try:\n",
    "                            qid_list.append(i['datavalue']['value']['id'])\n",
    "                        except:\n",
    "                            print(i,qv,qk)\n",
    "                            raise Exception('error')\n",
    "    if 'references' in p.keys():\n",
    "        for rp in p['references']: #p['references']是一个列表\n",
    "            for k,v in rp['snaks'].items():\n",
    "                if type(v) is dict:\n",
    "                    if 'wikibase-item' == v['datatype'] and v.get('datavalue',None) is not None:\n",
    "                        qid_list.append(v['datavalue']['value']['id'])\n",
    "                elif type(v) is list:\n",
    "                    for i in v:\n",
    "                        if 'wikibase-item' == i['datatype'] and i.get('datavalue',None) is not None:\n",
    "                            qid_list.append(i['datavalue']['value']['id'])\n",
    "    if len(qid_list)>0:\n",
    "        cnt+=1\n",
    "    item={}\n",
    "    item['claims_id']=p['id']\n",
    "    item['from_qid']=p['qid']\n",
    "    item['qid_list']=qid_list\n",
    "    claims3.append(item)\n",
    "\n",
    "print(len(claims3)) #142971\n",
    "claims3[:10]\n",
    "# 创建claims3表\n",
    "def create_relations_table(cursor, table_name, json_data):\n",
    "    # 创建表的SQL语句\n",
    "    create_table_sql = f\"CREATE TABLE IF NOT EXISTS {table_name} (\"\n",
    "    # 遍历json_data的键值对\n",
    "    for key, value in json_data.items():\n",
    "        # 如果值是字典，则递归调用create_table函数\n",
    "        if key == 'claims_id':\n",
    "            create_table_sql += f\"{key} VARCHAR(255) PRIMARY KEY, \"\n",
    "        elif key == 'qid_list':\n",
    "            create_table_sql += f\"{key} JSON, \"\n",
    "        else:\n",
    "            create_table_sql += f\"{key} VARCHAR(255), \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    create_table_sql = create_table_sql.rstrip(\", \")\n",
    "    # 添加右括号和分号\n",
    "    create_table_sql += \");\"\n",
    "    print(create_table_sql)\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(create_table_sql)\n",
    "# 插入数据到claims3表中\n",
    "def insert_data_into_relations_table(cursor, table_name, json_data):\n",
    "    if query_claims3_by_id(cursor, table_name, json_data['claims_id']):\n",
    "        print('Data already exists.')\n",
    "        return\n",
    "    # 插入数据的SQL语句\n",
    "    insert_data_sql = f\"INSERT INTO {table_name} (\"\n",
    "    # 遍历json_data的键值对\n",
    "    keys=json_data.keys()\n",
    "    values=json_data.values()\n",
    "    for key in keys:\n",
    "        insert_data_sql += f\"{key}, \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    insert_data_sql = insert_data_sql.rstrip(\", \")\n",
    "    # 添加右括号和values\n",
    "    insert_data_sql += \") VALUES (\"\n",
    "    for value in values:\n",
    "        if type(value) is str: \n",
    "            insert_data_sql += f\"'{value}', \"\n",
    "        elif type(value) is list:\n",
    "            insert_data_sql += f\"'{json.dumps(value)}', \"\n",
    "    # 删除最后一个逗号和空格\n",
    "    insert_data_sql = insert_data_sql.rstrip(\", \")\n",
    "    # 添加右括号和分号\n",
    "    insert_data_sql += \");\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(insert_data_sql)\n",
    "    conn.commit()\n",
    "# 查询cliams3表\n",
    "def query_claims3_by_id(cursor, table_name, id):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE claims_id = '{id}';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    print(result)\n",
    "    return result\n",
    "# 根据qid查询query3表\n",
    "def query_query3_by_qid(cursor, table_name, qid):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE qid = '{qid}';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "conn=pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "create_relations_table(cursor, 'claims3', claims3[0])\n",
    "# insert_data_into_relations_table(cursor, 'claims3', claims3[0])\n",
    "for c in claims3:\n",
    "    insert_data_into_relations_table(cursor, 'claims3', c)\n",
    "# query_claims3_by_id(cursor, 'claims3', claims3[0]['claims_id'])\n",
    "# query_query3_by_qid(cursor, 'query3', claims3[0]['from_qid'])\n",
    "# 关闭连接\n",
    "conn.close()\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询3的求解 查询与所给实体同时出现在一个声明的实体\n",
    "for c in claims3:\n",
    "    if len(c['qid_list'])>1:\n",
    "        print(c)\n",
    "        break\n",
    "# 查询3的两种理解： 基于claims3表，1.查询from_qid，返回qid_list中所有的qid; 2.查询所有qid_list，返回所有与qid同时出现的qid\n",
    "# 下面是基于第二种理解的查询\n",
    "query3={}\n",
    "for c in claims3:\n",
    "    if len(c['qid_list'])>1:\n",
    "        for qid in c['qid_list']:\n",
    "            if qid not in query3.keys():\n",
    "                query3[qid]=set(c['qid_list'])\n",
    "            else:\n",
    "                query3[qid]=query3[qid].union(set(c['qid_list']))\n",
    "#把query3字典类型转为列表类型\n",
    "query3_list=[]\n",
    "for k,v in query3.items():\n",
    "    item={}\n",
    "    item['qid']=k\n",
    "    item['qid_list']=list(v)\n",
    "    query3_list.append(item)\n",
    "query3_list[:10]\n",
    "# 创建query3表\n",
    "def create_query3_table(cursor, table_name, json_data):\n",
    "    # 创建表的SQL语句\n",
    "    create_table_sql = f\"CREATE TABLE IF NOT EXISTS {table_name} (qid VARCHAR(255) PRIMARY KEY, qid_list JSON);\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(create_table_sql)\n",
    "# 插入数据到query3表中\n",
    "def insert_data_into_query3_table(cursor, table_name, json_data):\n",
    "    if query_query3_by_id(cursor, table_name, json_data['qid']):\n",
    "        # print('Data already exists.')\n",
    "        return\n",
    "    # 插入数据的SQL语句\n",
    "    insert_data_sql = f\"INSERT INTO {table_name} (qid, qid_list) VALUES ('{json_data['qid']}', '{json.dumps(json_data['qid_list'])}');\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(insert_data_sql)\n",
    "    conn.commit()\n",
    "# 查询query3表\n",
    "def query_query3_by_id(cursor, table_name, id):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE qid = '{id}';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "conn = pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "create_query3_table(cursor, 'query3', query3_list[0])\n",
    "# insert_data_into_query3_table(cursor, 'query3', query3_list[0])\n",
    "for q in query3_list:\n",
    "    insert_data_into_query3_table(cursor, 'query3', q)\n",
    "query_query3_by_id(cursor, 'query3', query3_list[0]['qid'])\n",
    "# 关闭连接\n",
    "conn.close()\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询4的求解，直接查询item_pp表即可。\n",
    "\n",
    "# 综上，需要建立的表： items表， item_pp表， claims2表，relations表，claims3表，query3表\n",
    "# 对于查询5，还需要考虑属性的含义。\n",
    "# 查询items表中所有qid,labels, descriptions,claims(包含claims_id?)\n",
    "# 查询item_pp表中所有（包含claims_id,属性类型，属性值，属性含义，属性id）\n",
    "# 属性含义表(pid,label,des)\n",
    "# 查询属性属性含义表-->\n",
    "# 查询items表中labels。\n",
    "import pymysql\n",
    "import json\n",
    "\n",
    "\n",
    "# 连接数据库\n",
    "conn= pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "# 查询items表\n",
    "def query_items_byID(cursor, table_name, id):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT id,JSON_EXTRACT(labels, '$.en.value'),JSON_EXTRACT(descriptions, '$.en.value') FROM {table_name} WHERE id = '{id}';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "# 查询item_pp表\n",
    "def query_item_pp_byPID(cursor, table_name, pid):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE pid = '{pid}' and rank1 != 'deprecated';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "# 查询items表中所有qid,labels, descriptions,claims(包含claims_id?)\n",
    "query_items_byID(cursor, 'items', 'Q31')\n",
    "# 查询item_pp表中所有（包含claims_id,属性类型，属性值，属性含义，属性id）\n",
    "res=query_item_pp_byPID(cursor, 'item_pp', 'P1082') # P585\n",
    "last_qid=None\n",
    "q5_list=[]\n",
    "for r in res:\n",
    "    mainsnak=json.loads(r[2])\n",
    "    if r[4]:\n",
    "        qualifiers=json.loads(r[4])\n",
    "    qid=r[0]\n",
    "    if last_qid!=qid:\n",
    "        item_res=query_items_byID(cursor, 'items', qid)\n",
    "        last_qid=qid\n",
    "        tmp=item_res\n",
    "    else:\n",
    "        tmp=item_res\n",
    "    item={}\n",
    "    item['qid']=qid\n",
    "    item['label']=tmp[0][1]\n",
    "    item['des']=tmp[0][2]\n",
    "    item['population']=mainsnak['datavalue']['value']['amount']\n",
    "    item['time']=''\n",
    "    if r[4] and 'P585' in qualifiers:\n",
    "        item['time']=qualifiers['P585'][0]['datavalue']['value']['time']\n",
    "    q5_list.append(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sentence_transformers import SentenceTransformer\n",
    "sentences = [\"This is an example sentence\", \"Each sentence is converted\"]\n",
    "\n",
    "model = SentenceTransformer('sentence-transformers/all-MiniLM-L6-v2')\n",
    "query5_list=[]\n",
    "for q in q5_list:\n",
    "    text=q['label']+' '+q['des']+' population: '+q['population']+', time: '+q['time']\n",
    "    embed = model.encode(text)\n",
    "    item=q.copy()\n",
    "    item['embed']=embed.tolist()\n",
    "    query5_list.append(item)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 根据query5_list中的数据，创建query5表\n",
    "import pymysql\n",
    "import json\n",
    "# 建表  \n",
    "def create_query5_table(cursor, table_name):\n",
    "    # 创建表的SQL语句\n",
    "    create_table_sql = f\"CREATE TABLE IF NOT EXISTS {table_name} (qid VARCHAR(255), label VARCHAR(255), des VARCHAR(255), population VARCHAR(255), time VARCHAR(255), embed JSON);\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(create_table_sql)\n",
    "# 插入数据\n",
    "def insert_data_into_query5_table(cursor, table_name, data):\n",
    "    # 插入数据的SQL语句\n",
    "    insert_data_sql = f\"INSERT INTO {table_name} (qid, label, des, population, time, embed) VALUES (%s, %s, %s, %s, %s, %s);\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(insert_data_sql, (data['qid'], data['label'], data['des'], data['population'], data['time'], json.dumps(data['embed'])))\n",
    "    # 提交事务\n",
    "    conn.commit()\n",
    "# 查询数据\n",
    "def query_query5_by_id(cursor, table_name, id):\n",
    "    # 查询数据的SQL语句\n",
    "    query_data_sql = f\"SELECT * FROM {table_name} WHERE qid = '{id}';\"\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(query_data_sql)\n",
    "    # 获取查询结果\n",
    "    result = cursor.fetchall()\n",
    "    # print(result)\n",
    "    return result\n",
    "# 连接数据库\n",
    "conn= pymysql.connect(host='localhost', user='root', password='123456', database='wikidata')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# 测试\n",
    "create_query5_table(cursor, 'query5')\n",
    "for q in query5_list:\n",
    "    insert_data_into_query5_table(cursor, 'query5', q)\n",
    "print(query_query5_by_id(cursor, 'query5', 'Q31'))\n",
    "# 关闭连接\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
